﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.ServiceModel;
using System.Data.Sql;
using System.Data;
using AuctionModel;

namespace DataBaseController
{
    public static class DbCtr
    {
        public static void CreateUser(User user)
        {
            WriteSQL("INSERT INTO [User] (Role, Password, Name, Department, Ssn, Address, ZipCode, City, PhoneNo, Email, BidPoints) VALUES (" +
                "@ROLE, @PASSWORD, @NAME, @DEPARTMENT, @SSN, @ADDRESS, @ZIPCODE, @CITY, @PHONENO, @EMAIL, @BIDPOINTS)", new object[][] 
                { new string[] {  "@ROLE", user.Role.ToString() }, new string[] { "@PASSWORD", user.Password }, 
                    new string[] { "@NAME", user.Name}, new string[] { "@DEPARTMENT", user.Department}, new object[] { "@SSN", user.Ssn}, 
                    new string[] { "@ADDRESS", user.Address}, new object[] { "@ZIPCODE", user.ZipCode}, new string[] { "@CITY", user.City },
                    new object[] { "@PHONENO", user.PhoneNo}, new string[] { "@EMAIL", user.Email}, new object[] { "@BIDPOINTS", user.BidPoints} });
        }
        public static bool UserExist(User user)
        {
            return Convert.ToBoolean(
            WriteSQL("SELECT CAST(COUNT(1) AS BIT) AS Expr1 FROM [User] WHERE [User].Email = @EMAIL OR [User].Ssn = @SSN", new object[][]
            {new string[] { "@EMAIL", user.Email}, new object[] {"@SSN", user.Ssn}}));

        }
        public static User LogIn(string username, string password)
        {
            SqlDataReader data = ReadSQL("SELECT * FROM [User] WHERE [User].Email = @USERNAME AND [User].Password = @PASSWORD", 
                new object[][] { new string[] { "@USERNAME", username }, new string[] { "@PASSWORD", password } }); 
            
            if (data != null && data.Read())
            {
                return new User(data.GetInt32(0), (Roles)Enum.Parse(typeof(Roles), data.GetString(1)), data.GetString(2), 
                    data.GetString(3), data.GetString(4), data.GetInt32(5), data.GetString(6), data.GetInt16(7), data.GetString(8), 
                    data.GetInt32(9), data.GetString(10), data.GetDouble(11));
            }
            return null;
        }
        public static void CreateItem(Item item)
        {
            WriteSQL(
                "INSERT INTO [ITEM] (Title, Description, StartPrice, EndPrice, StartDateTime, EndDateTime, Active) VALUES(' " +
                "@TITLE, @DESCRIPTION, @STARTPRICE, @ENDPRICE, @STARTDATETIME, @ENDDATETIME, @ACTIVE')", new string[][]
                    {
                        new string[] {"@TITLE", item.Title}, new string[] {"@DESCRIPTION", item.Description},
                        new string[] {"@STARTPRICE", item.StartPrice.ToString()},
                        new string[] {"@ENDPRICE", item.EndPrice.ToString()}, new string[] {"@STARTDATETIME", item.StartDateTime.ToString()},
                        new string[] {"@ENDDATETIME", item.EndDateTime.ToString()}, new string[] {"@ACTIVE", item.Active.ToString()}
                    });
        }
        public static void CreateAuction(Auction auction)
        {
            WriteSQL(
                "INSERT INTO [AUCTION] (Title, Description, Date) VALUES(" +
                "@TITLE, @DESCRIPTION, @DATE)", new string[][]
                    {
                        new string[] {"@TITLE", auction.Title},
                        new string[] {"@DESCRIPTION", auction.Description},
                        new string[] {"@DATE", auction.Date.ToString("yyyy-MM-dd 00:00:00")}
                    }
                );
        }
        public static Auction GetAuction(int id)
        {
            SqlDataReader data = ReadSQL("SELECT * FROM [Auction] WHERE [Auction].Id = @ID", new object[][]
                {
                    new string[] { "@ID", id.ToString()}
                });

            if (data != null && data.Read())
            {
                return new Auction(data.GetString(2), GetItemsInAuction(id), data.GetString(1), data.GetInt32(0));
            }
            return null;
        }
        public static List<Item> GetItemsInAuction(int auctionId)
        {
           var returnList = new List<Item>();

           SqlDataReader data = ReadSQL("SELECT * FROM [Item] join [AuctionItems] on [Item].Id = [AuctionItems].ItemId WHERE [AuctionItems].AuctionId = @AUCTIONID", new object[][]
                {
                    new string[] {"@AUCTIONID", auctionId.ToString() }
                });

           while (data.Read())
           {
               Item retItem = new Item();
               retItem.ID = data.GetInt32(0);            
               retItem.Title = data.GetString(1);                
               retItem.Description = data.GetString(2);                
               retItem.StartPrice = data.GetDouble(3);
               if(!data.IsDBNull(4))
               {
                    retItem.EndPrice = data.GetDouble(4);    
               }                           
               retItem.StartDateTime = data.GetDateTime(5);
               retItem.EndDateTime = data.GetDateTime(6);                
               retItem.Active = data.GetBoolean(7);
               returnList.Add(retItem);
               
           }
            if (returnList.Count > 0)
            {
                return returnList;
            }
            return null;
        }
        public static void BidOnItem(Bid bid)
        {
            WriteSQL(
                "INSERT INTO BID (UserId, Amount, DateTimePlaced, ItemId) VALUES(" +
                "@USERID, @AMOUNT, @DATETIMEPLACED, @ITEMID)", new string[][]
                {
                    new string [] {"@USERID", bid.User.ID.ToString()},
                    new string[] {"@AMOUNT", bid.Amount.ToString()},
                    new string[] {"@DATETIMEPLACED", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")},
                    new string[] {"@ITEMID", bid.Item.ID.ToString()},
                }
                    );
        }
        public static int GetTopBid(Item item)
        {
            /*
             * 
             * BUIlD USER
             * */
            int retInt;
            retInt = new Int32();
            int personId = new Int32();

            SqlDataReader data = ReadSQL("SELECT * FROM [BID] WHERE ItemId=@ItemId AND Amount=(SELECT max(Amount) from BID)", new object[][]
            {
                new string[] {"@ItemId", item.ID.ToString()}
            }
            );
            while(data.Read())
            {
               retInt = (int)data.GetDouble(2);
               personId = data.GetInt32(1);
            }
            return retInt;

        }
        public static void AddItemToAuction(Item item, Auction auction)
        {
            WriteSQL(
                "INSERT INTO [AuctionItems] (AuctionId, ItemId) VALUES(' " +
                "@AUCTIONID, @ITEMID ')", new string[][]
                    {
                        new string[] {"@AUCTIONID", auction.ID.ToString()},
                        new string[] {"@ITEMID", item.ID.ToString()},
                    }
                );
        }
        public static List<Auction> ShowAllAuctions()
        {
            var returnList = new List<Auction>();
            SqlDataReader data = ReadSQL("SELECT * FROM Auction");

            while(data.Read())
            {
                Auction auction = new Auction();
                auction.ID = data.GetInt32(0);
                auction.Title = data.GetString(1);
                auction.Description = data.GetString(2);
                auction.Date = data.GetDateTime(3);
                auction.Items = GetItemsInAuction(auction.ID);
                returnList.Add(auction);
            }
            return returnList;
        }
        public static List<Item> GetAllItems()
        {
            var returnList = new List<Item>();
            SqlDataReader data = ReadSQL("SELECT * FROM Item");

            while(data.Read())
            {
                Item item = new Item();
                item.ID = data.GetInt32(0);
                item.Title = data.GetString(1);
                item.Description = data.GetString(2);
                item.StartPrice = data.GetDouble(3);
                item.EndPrice = data.GetDouble(4);
                item.StartDateTime = data.GetDateTime(5);
                item.EndDateTime = data.GetDateTime(6);
                item.Active = data.GetBoolean(7);
            }
            return returnList;
        }

        /// <summary>
        /// Read from the Database
        /// </summary>
        /// <param name="cmd">All SQL commands that will be executed.</param>
        /// <param name="val">Variables for parameterized queries.</param>
        /// <returns>Returns an object from the command.</returns>
        private static SqlDataReader ReadSQL(string cmd, params object[][] val)
        {
            try
            {
                SqlConnection sqlcon = new SqlConnection(Properties.Settings.Default.DBConnectionString);

                sqlcon.Open();
                SqlCommand sqlcmd = new SqlCommand(cmd, sqlcon);
                for (int i = 0; i < val.Length; i++)
                {
                    if (cmd == null || val[i][0] == null)
                    {
                        continue;
                    }
                    sqlcmd.Parameters.AddWithValue(val[i][0].ToString(), val[i][1]);
                }
                //sqlcmd.Dispose();
                return sqlcmd.ExecuteReader();
            }
            catch (SqlException ex)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
            }
            catch (InvalidOperationException ex)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
            }
            catch (Exception ex)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
            }
        }

        /// <summary>
        /// Writes command to the database.
        /// </summary>
        /// <param name="cmd">All SQL commands that will be executed.</param>
        /// <param name="val">Variables for parameterized queries.</param>
        /// <returns>Returns an object from the command.</returns>
        private static object WriteSQL(string cmd, params object[][] val)
        {
            try
            {
                using (SqlConnection sqlcon = new SqlConnection(Properties.Settings.Default.DBConnectionString))
                {
                    sqlcon.Open();
                    SqlCommand sqlcmd = new SqlCommand(cmd, sqlcon);
                    for (int i = 0; i < val.Length; i++)
                    {
                        if (cmd == null || val[i][0] == null)
                        {
                            continue;
                        }
                        sqlcmd.Parameters.AddWithValue(val[i][0].ToString(), val[i][1]);
                    }
                    return sqlcmd.ExecuteScalar();
                }
            }
            catch (SqlException ex)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
            }
            catch (InvalidOperationException ex)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
            }
            catch (Exception ex)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
            }
        }

        /// <summary>
        /// Start a transaction in the database.
        /// </summary>
        /// <param name="cmd">All SQL commands that will be executed in the transaction.</param>
        /// <param name="val">Variables for parameterized queries.</param>
        private static void TransactSQL(string[] cmd, params object[][] val)
        {
            try
            {
                using (SqlConnection sqlcon = new SqlConnection(Properties.Settings.Default.DBConnectionString))
                {
                    sqlcon.Open();
                    SqlTransaction sqltransaction = sqlcon.BeginTransaction(IsolationLevel.ReadUncommitted, "Order");
                    for (int i = 0; i < cmd.Length; i++)
                    {
                        if (cmd[i] == null)
                        {
                            continue;
                        }
                        SqlCommand sqlcmd = new SqlCommand(cmd[i], sqlcon, sqltransaction);
                        for (int j = 0; j < (int)(val.Length / cmd.Length); j++)
                        {
                            if (val[(val.Length / cmd.Length * i) + j][0] == null)
                            {
                                continue;
                            }
                            sqlcmd.Parameters.AddWithValue(val[(val.Length / cmd.Length * i) + j][0].ToString(), val[(val.Length / cmd.Length * i) + j][1]);
                        }
                        sqlcmd.ExecuteScalar();
                    }
                    try
                    {
                        sqltransaction.Commit();
                    }
                    catch (Exception ex)
                    {
                        //If this happens something is wrong...
                        try
                        {
                            sqltransaction.Rollback();
                        }
                        //If any of this happens some one must really have fucked things up...
                        catch (InvalidOperationException exce)
                        {
                            throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + exce.ToString());
                        }
                        catch (Exception exce)
                        {
                            throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + exce.ToString());
                        }
                        throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
                    }
                }
            }
            catch (SqlException ex)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
            }
            catch (InvalidOperationException ex)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
            }
            catch (Exception ex)
            {
                throw new FaultException("Data access to server was abnormal, copy this and send to the admins of the page: " + ex.ToString());
            }
        }
    }
}
